#!/usr/bin/env bash
# Filename     :	docker-install-ubuntu22.sh
# Last modified:	2024-05-11 12:12
# Version      :
# Author       : jack.zang
# Email        : jack.zang@aishangwei.net
# Description  :
# 使用方法：(MYSQL_PASS="xdb5vRs2" && source <(curl -sL https://gitee.com/jack_zang/public-scripts/raw/master/shell/mysql/mysql8_install_docker.sh))
# ******************************************************

mkdir -p /data/mysql8/data
cat > /data/mysql8/my.cnf <<EOF
[mysqld]
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
thread_cache_size = 50 # 线程缓存大小，减少线程创建开销
table_open_cache = 2000 # 表缓存大小，依据最大并发量调整
# 内存分配（关键调整）
innodb_buffer_pool_size = 8G           # 建议为物理内存的60-70%
innodb_buffer_pool_instances = 8        # 缓冲池实例数，每实例不超过4GB
innodb_log_file_size = 2G               # 单个日志文件大小
innodb_log_files_in_group = 3           # 日志文件组数
innodb_flush_log_at_trx_commit = 2      # 权衡性能与持久性
sort_buffer_size = 8M                   # 排序缓冲区
join_buffer_size = 8M                   # 连接缓冲区
read_rnd_buffer_size = 16M              # 随机读缓冲区
tmp_table_size = 256M                   # 临时表最大内存大小
max_heap_table_size = 256M              # 最大堆表大小

# 并发控制
max_connections = 1024                 # 根据实际并发需求调整
back_log = 2048                         # 监听队列长度
max_connect_errors = 100000
thread_cache_size = 128                 # 线程缓存数

# 查询优化
optimizer_switch = 'index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on'
innodb_stats_on_metadata = 0

# 存储引擎优化
default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT          # 直接I/O模式
innodb_io_capacity = 4000               # IOPS估计值
innodb_io_capacity_max = 8000           # 最大IOPS
innodb_read_io_threads = 16             # 读IO线程数
innodb_write_io_threads = 16            # 写IO线程数
innodb_purge_threads = 4                # 清除线程数

# 日志和查询优化
expire_logs_days = 14                  # 二进制日志保留天数
general_log = 0                        # 关闭通用日志（影响性能）
slow_query_log = 1 # 开启慢查询日志
long_query_time = 1 # 慢查询阈值（秒）
log_queries_not_using_indexes = 1 # 记录未使用索引的查询

# 字符集
character-set-server = utf8mb4 # 设置字符集为utf8mb4以支持更多字符类型
collation-server = utf8mb4_unicode_ci # 设置校对规则

# 其他
skip-name-resolve # 跳过域名解析，加快连接速度
default-time-zone = '+8:00'
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
EOF
docker run --name mysql8 -d --restart=always -p 3306:3306 \
      -v /data/mysql8/data:/var/lib/mysql \
      -v /data/mysql8/my.cnf:/etc/mysql/my.cnf \
      -e MYSQL_ROOT_PASSWORD=${MYSQL_PASS}  --privileged=true \
      mysql:8.0.13









